
// Description: This creates a dataset containing the case total per year from 2008FY-2017FY for Florida.

clear

cd "~/Documents/Research/data/malpractice case data/Florida"

import excel "~/Documents/Research/data/malpractice case data/Florida/Most Recent MPL Closed Claims/MPL Current 8-4-2020.xlsx", sheet("plcr2doh4OIR") firstrow allstring

keep MPL_DEPT_FILE_NUM MPL_FIN_DATE_DISP MPL_INDEMNITY_PAID MPL_SUIT_COUNTY MPL_OCCURRENCE_DATE

// Remove payments made before May 1, 2008 for fair comparison with NC data

rename MPL_FIN_DATE_DISP date

gen splitat = ustrpos(date,"/")
gen month = usubstr(date, 1, splitat-1)
gen partialDate = usubstr(date, splitat+1, .)
replace splitat = ustrpos(partialDate,"/")
gen year = usubstr(partialDate, splitat+1, 4)

gen month_r = real(month)
gen year_r = real(year)

drop month year

rename month_r month
rename year_r year

drop if (year<=2007) | (month<5 & year==2008)

// Remove payments less than $75,000 for fair comparison with NC data

rename MPL_INDEMNITY_PAID payment

gen payment_r = real(payment)

drop if payment_r<75000

// Use unique file identifier variable to eliminate extra counting of the same case

sort MPL_DEPT_FILE_NUM
quietly by MPL_DEPT_FILE_NUM:  gen dup = cond(_N==1,0,_n)
drop if dup>1

// Rename remaining variables

keep MPL_SUIT_COUNTY MPL_OCCURRENCE_DATE

rename MPL_SUIT_COUNTY county
rename MPL_OCCURRENCE_DATE date

// Convert years to fiscal years

gen splitat = ustrpos(date,"/")
gen month = usubstr(date, 1, splitat-1)
gen partialDate = usubstr(date, splitat+1, .)
replace splitat = ustrpos(partialDate,"/")
gen year = usubstr(partialDate, splitat+1, 4)

gen month_r = real(month)
gen year_r = real(year)

drop month year

rename month_r month
rename year_r year

keep if inrange(year, 2007, 2017)

gen     part =.
replace part =1 if month <=9
replace part =2 if month > 9 & month <=12

gen FisYear=.
replace FisYear=2008 if (year==2007 & part==2) | (year==2008 & part==1)
replace FisYear=2009 if (year==2008 & part==2) | (year==2009 & part==1)
replace FisYear=2010 if (year==2009 & part==2) | (year==2010 & part==1)
replace FisYear=2011 if (year==2010 & part==2) | (year==2011 & part==1)
replace FisYear=2012 if (year==2011 & part==2) | (year==2012 & part==1)
replace FisYear=2013 if (year==2012 & part==2) | (year==2013 & part==1)
replace FisYear=2014 if (year==2013 & part==2) | (year==2014 & part==1)
replace FisYear=2015 if (year==2014 & part==2) | (year==2015 & part==1)
replace FisYear=2016 if (year==2015 & part==2) | (year==2016 & part==1)
replace FisYear=2017 if (year==2016 & part==2) | (year==2017 & part==1)

// Keep entire range from 2008-2017
drop if FisYear==.

//Limit geography to FL
    //Remove cases with no county (might not be in FL)

drop if county=="" | county=="10CA-004822" | county=="11-2771ca32" | county=="Out of state" | county=="2015-CA-000588-O" | county=="50-2013CA013276-XXXX" | county=="CAL13-03414" 

keep FisYear 

// Collapse to year

sort FisYear
by FisYear: gen tot_cases = _N

duplicates drop

gen state="FL"

// Save dataset

save "~/Desktop/FL_mal_cases_08to17", replace

